﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web.Http;

namespace Waiyun.WebApi
{
    public class WTSLDController : ApiController
    {
        public static string ConnectionString;

        static WTSLDController()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["waiyun"].ConnectionString;
        }

        [HttpGet]
        public SingleResult<long> Total(string name)
        {
            if (string.Equals(name, "order", StringComparison.CurrentCultureIgnoreCase))
            {
                return TotalOrder();
            }
            else if (string.Equals(name, "warn", StringComparison.CurrentCultureIgnoreCase))
            {
                return TotalWarning();
            }
            else if (string.Equals(name, "cust", StringComparison.CurrentCultureIgnoreCase))
            {
                return TotalCustomer();
            }
            return new SingleResult<long>(0);
        }
        #region total
        public SingleResult<long> TotalWarning()
        {
            string errorMessage = null;
            OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
            if (connection == null)
            {
                return new SingleResult<long>(1, errorMessage);
            }

            try
            {
                long value = 0;
                using (OracleCommand command = new OracleCommand(@"select count(1) as total
from BW_BUSINESS_WARNING_INFO B
where B.create_time > sysdate-30", connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            value = Convert.ToInt64(reader["total"]);
                        }
                        reader.Close();
                        return new SingleResult<long>(value);
                    }
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<long>(1, ex.Message);
            }
            finally
            {
                ConnectionPool.Release(connection);
            }

            //            using (OracleConnection connection = new OracleConnection(ConnectionString))
            //            {

            //                try
            //                {
            //                    connection.Open();

            //                    long value = 0;
            //                    using (OracleCommand command = new OracleCommand(@"select count(1) as total
            //from BW_BUSINESS_WARNING_INFO B
            //where B.create_time > sysdate-30", connection))
            //                    {
            //                        using (OracleDataReader reader = command.ExecuteReader())
            //                        {
            //                            if (reader.Read())
            //                            {
            //                                value = Convert.ToInt64(reader["total"]);
            //                            }
            //                            reader.Close();
            //                            return new SingleResult<long>(value);
            //                        }
            //                    }
            //                }
            //                catch (Exception ex)
            //                {
            //                    return new SingleResult<long>(1, ex.Message);
            //                }
            //                finally
            //                {
            //                    connection.Close();
            //                }
            //            }
        }

        private SingleResult<long> TotalOrder()
        {
            string errorMessage = null;
            OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
            if (connection == null)
            {
                return new SingleResult<long>(1, errorMessage);
            }
            try
            {
                long value = 0;
                using (OracleCommand command = new OracleCommand(@"select count(1) as total from df_order_status_head 
where CREATE_TIME > sysdate-30", connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {

                        if (reader.Read())
                        {
                            value = Convert.ToInt64(reader["total"]);
                        }
                        reader.Close();
                        return new SingleResult<long>(value);
                    }
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<long>(1, ex.Message);
            }
            finally
            {
                ConnectionPool.Release(connection);
            }

//            using (OracleConnection connection = new OracleConnection(ConnectionString))
//            {
//                try
//                {
//                    connection.Open();

//                    long value = 0;
//                    using (OracleCommand command = new OracleCommand(@"select count(1) as total from df_order_status_head 
//where CREATE_TIME > sysdate-30", connection))
//                    {
//                        using (OracleDataReader reader = command.ExecuteReader())
//                        {

//                            if (reader.Read())
//                            {
//                                value = Convert.ToInt64(reader["total"]);
//                            }
//                            reader.Close();
//                            return new SingleResult<long>(value);
//                        }
//                    }
//                }
//                catch (Exception ex)
//                {
//                    return new SingleResult<long>(1, ex.Message);
//                }
//                finally
//                {
//                    connection.Close();
//                }
//            }
        }
        public SingleResult<long> TotalCustomer()
        {
            return new SingleResult<long>(9);

//            OracleConnection connection = new OracleConnection(ConnectionString);
//            try
//            {
//                connection.Open();

//                long value = 0;
//                OracleCommand command = new OracleCommand(string.Format(@"select count(1) as total
//from df_order_status_head h
//where h.create_time > TO_DATE('{0}', 'YYYY-MM-DD') and h.DFOH_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ELFD00000012','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')", DateTime.Now.ToString("yyyy-MM-dd")), connection);
//                OracleDataReader reader = command.ExecuteReader();
//                if (reader.Read())
//                {
//                    value = Convert.ToInt64(reader["total"]);
//                }
//                reader.Close();
//                return new SingleResult<long>(value);
//            }
//            catch (Exception ex)
//            {
//                return new SingleResult<long>(1, ex.Message);
//            }
//            finally
//            {
//                if (connection.State == System.Data.ConnectionState.Open)
//                {
//                    connection.Close();
//                }
//            }
        }
        #endregion

        [HttpGet]
        public SingleResult<Data<string, long>> XY(string name)
        {
            if (string.Equals(name, "order", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYOrder();
            }
            else if (string.Equals(name, "cust", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYCust();
            }
            else if (string.Equals(name, "warn", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYWarn();
            }
            return new SingleResult<Data<string, long>>(1, "unknown");
        }

        private SingleResult<Data<string, long>> XYOrder()
        {
            Data<string, long> cacheData = Cache.Get("WTSLD-XYORDER") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string,long>>(cacheData);
            }

            OracleConnection connection = new OracleConnection(ConnectionString);
            try
            {
                connection.Open();

                using (OracleCommand command = new OracleCommand(@"select count(1) as Y,to_char(CREATE_TIME,'yyyy-mm-dd') as X from df_order_status_head h
where h.create_time>sysdate-30  group by to_char(CREATE_TIME,'yyyy-mm-dd')
　　order by to_char(CREATE_TIME,'yyyy-mm-dd')", connection))
                {
                    OracleDataAdapter adapter = new OracleDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);

                    Data<string, long> data = new Data<string, long>();
                    for (int index = 0; index < dataTable.Rows.Count; index++)
                    {
                        data.XValues.Add(Convert.ToString(dataTable.Rows[index]["X"]));
                        data.YValues.Add(Convert.ToInt64(dataTable.Rows[index]["Y"]));
                    }

                    Cache.Set("WTSLD-XYORDER", data);
                    return new SingleResult<Data<string, long>>(data);
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<Data<string, long>>(1, ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        private SingleResult<Data<string, long>> XYWarn()
        {
            Data<string, long> cacheData = Cache.Get("WTSLD-XYWARN") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                try
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(@"select DFSD_NAME as X, ORDERSUM as Y from (select * from (
select d.dfsd_name,count(1) as orderSum from BW_BUSINESS_WARNING_INFO B,df_status_definition d
where b.bbwi_status_code=d.dfsd_code
and B.create_time > sysdate-100
group by d.dfsd_name) A
order by A.orderSum desc) AA
where rownum <=5", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            Data<string, long> data = new Data<string, long>();
                            for (int index = 0; index < dataTable.Rows.Count; index++)
                            {
                                data.XValues.Add(Convert.ToString(dataTable.Rows[index]["X"]));
                                data.YValues.Add(Convert.ToInt64(dataTable.Rows[index]["Y"]));
                            }

                            Cache.Set("WTSLD-XYWARN", data);
                            return new SingleResult<Data<string, long>>(data);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return new SingleResult<Data<string, long>>(1, ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private SingleResult<Data<string, long>> XYCust()
        {
            Data<string, long> cacheData = Cache.Get("WTSLD-XYCUST") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                try
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(@"select (case h.DFOH_STANDARD_CUST_ID 
when 'ESDH00001238' then '魏桥纺织'
when 'ESDY00100247' then '青岛前程环球海空通运'
when 'ESDY00100639' then '青岛和川国际物流'
when 'ESDY00101058' then '青岛瑞福达'
when 'ESDY00101122' then '青岛浩宏'
when 'ESDY00101572' then '山东澳源达'
when 'ETJF00001177' then '天津德迅'
when 'ETJF00001213' then '天津敦豪'
when 'ETJF00001592' then '天津海斯陆'
else h.DFOH_STANDARD_CUST_ID end) as X,count(1) as Y from df_order_status_head h where  h.create_time > sysdate-30 group by h.DFOH_STANDARD_CUST_ID 
having h.DFOH_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')
order by Y desc", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            Data<string, long> data = new Data<string, long>();
                            for (int index = 0; index < dataTable.Rows.Count; index++)
                            {
                                data.XValues.Add(Convert.ToString(dataTable.Rows[index]["X"]));
                                data.YValues.Add(Convert.ToInt64(dataTable.Rows[index]["Y"]));
                            }

                            Cache.Set("WTSLD-XYCUST", data);
                            return new SingleResult<Data<string, long>>(data);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return new SingleResult<Data<string, long>>(1, ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
